package com.skycode.dao;
import  com.skycode.bean.NoticeBean;

import com.skycode.util.DBConnection;
import com.skycode.util.Time;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class NoticeDao {
    DBConnection db = new DBConnection();
    public int add(NoticeBean notice){
        Connection conn = db.getConn();//conn代表数据库链接
        String sql = "insert into notice values(null,?,?,?,?)";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,notice.getNoticeTitle());
            ps.setString(2,notice.getNoticeContent());
            ps.setString(3, Time.getDateTime());//时间优化
            ps.setString(4,notice.getAdminName());
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("插入news表异常");
        }
        return 0;

    }
//    删除
    public int delete (int NoticeID){
        Connection conn = db.getConn();//conn代表数据库链接

        String sql = "delete from notice where Noticeid=?";
        try {
            PreparedStatement ps =  conn.prepareStatement(sql);
            ps.setInt(1,NoticeID);
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("删除news表异常");
        }
        return 0;
    }
//    更新
    public int update(NoticeBean notice){
        Connection conn = db.getConn();//conn代表数据库链接
        String sql = "update notice set NoticeTitle=?,NoticeContent=?,NoticeTime=?,AdminName=? where Noticeid=?";//使用jdbc预处理对象添加
        try {
            PreparedStatement ps =  conn.prepareStatement(sql);
            ps.setString(1,notice.getNoticeTitle());
            ps.setString(2,notice.getNoticeContent());
            ps.setString(3,Time.getDateTime());//时间优化
            ps.setString(4,notice.getAdminName());
            ps.setInt(5,notice.getNoticeId());
            return ps.executeUpdate();

        } catch (SQLException e) {
            System.out.println("更新notice表异常");
        }
        return 0;
    }
    public List<NoticeBean> getList(int page){
        Connection conn = db.getConn();//conn代表数据库链接
        String sql = "select *from notice limit ?,?";//第一个开始的行数 第二个是查询显示的条数
        List<NoticeBean> list = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,(page-1)*10);//page为1是行数为0 page为2时 10行
            ps.setInt(2,10);// 10 表示10条数
            ResultSet rs = ps.executeQuery();//rs 是数据查询结果集 全部是字符串
            while (rs.next()){
                NoticeBean notice =new NoticeBean();//每轮循环生命一个新闻对象
                notice.setNoticeId(rs.getInt("NoticeId"));//rs 获取该行新闻ID 通过新闻对象封装方法设置新闻值notices .setNewsTitle(rs.getString("NewsTitle"));
                notice.setNoticeTitle(rs.getString("NoticeTitle"));
                notice.setNoticeContent(rs.getString("NoticeContent"));
                notice.setNoticeTime(rs.getString("NoticeTime"));
                notice.setAdminName(rs.getString("AdminName"));
                list.add(notice);//将设置好的新闻对象放入list中
            }
        } catch (SQLException e) {
            System.out.println("查询news表异常");
        }
        return list;//如果添加异常返回空
    }

    public int count() {
        Connection conn = db.getConn();
        String sql = "select count(*) from notice";
        try{
            Statement statement = conn.createStatement();
            ResultSet rs =statement.executeQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        }catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return 0;
    }
}
